from bottle import get, run, static_file, request, response, template, error, post
import pymysql
import re

#===== Pages =====
@get('/')
def home_page():
	print("--- building home page ---")
	searchQuery = ""
	searchResults = None
	searchQuery = request.query.query
	if "" != searchQuery:
		searchResults = get_search_results_from_db(searchQuery)
	return template('home', searchResults = searchResults)

@get('/about')	
def about_page():
	print("--- building about page ---")
	suppData = get_supplementary_data_from_db()
	return template('about', version = suppData[0]["software_version"], os = suppData[0]["os"], username = suppData[0]["username"], execution_time = suppData[0]["execution_time"])
	
@get('/favicon.ico')
def get_favicon():
	return static_file('favicon.ico', root='./')
	
@get('/<filepath:path>')
def get_file(filepath):
	return static_file(filepath, root='./')

@get('/artists')
def artists():
	print("--- building artists page ---")
	artistData = get_artists_from_db()
	return template('artists', artistData = artistData)
	
@get('/tracks')
def tracks():
	print("--- building tracks page ---")
	trackData = get_tracks_from_db()
	return template('tracks', trackData = trackData)
	
#===== Functions =====

def get_supplementary_data_from_db ():
	print("--- getting supplementary data ---")
	conn = pymysql.connect(host='mysql2.alwaysdata.com', port=3306, user='epamtest', passwd='e123456;', db='epamtest_q42013')
	cur = conn.cursor(pymysql.cursors.DictCursor)
	cur.execute("SELECT `software_version`, `os`, `username`, `execution_time` FROM `supplementary` ORDER BY `execution_time` DESC LIMIT 1;")
	suppData = cur.fetchall()
	print("Got ", cur.rowcount, " rows")
	cur.close()
	conn.close()
	return suppData
	
def get_artists_from_db ():
	print("--- getting artists data ---")
	conn = pymysql.connect(host='mysql2.alwaysdata.com', port=3306, user='epamtest', passwd='e123456;', db='epamtest_q42013')
	cur = conn.cursor(pymysql.cursors.DictCursor)
	cur.execute("select `name` from `artists` order by name;")
	artistData = cur.fetchall()
	print("Got ", cur.rowcount, " rows")
	cur.close()
	conn.close()
	return artistData
	
def get_tracks_from_db ():
	print("--- getting tracks data ---")
	conn = pymysql.connect(host='mysql2.alwaysdata.com', port=3306, user='epamtest', passwd='e123456;', db='epamtest_q42013')
	cur = conn.cursor(pymysql.cursors.DictCursor)
	cur.execute("SELECT tracks.track_id, tracks.track_name, artists.name, tracks.cover FROM tracks, artists WHERE tracks.artist_id = artists.id ORDER BY tracks.artist_id;")
	trackData = cur.fetchall()
	print("Got ", cur.rowcount, " rows")
	cur.close()
	conn.close()
	return trackData
	
def get_search_results_from_db (searchQuery):
	print("--- getting search results ---")
	conn = pymysql.connect(host='mysql2.alwaysdata.com', port=3306, user='epamtest', passwd='e123456;', db='epamtest_q42013')
	cur = conn.cursor(pymysql.cursors.DictCursor)
	cur.execute("select `name` from `artists` where `name` like '%" + re.escape(searchQuery) + "%' union select `track_name` from `tracks` where `track_name` like '%" + re.escape(searchQuery) + "%';")
	searchResults = cur.fetchall()
	print("Got ", cur.rowcount, " rows")
	cur.close()
	conn.close()
	return searchResults
	
#===== Main =====

run(host='localhost', port=8090, debug=True)